﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ZLDC.Common.Estate
{
    public class _Database : IDisposable
    {
        public SqlConnection Connection;
        public int num_rows;
        public SortedList row;
        public DataRowCollection rows;

        public _Database(string ConnectionString)
        {
            this.open(ConfigurationManager.AppSettings[ConnectionString]);
        }

        public _Database(string ConnectionString, bool IsConn)
        {
            if (IsConn)
            {
                this.open(ConnectionString);
            }
            else
            {
                this.open(ConfigurationManager.AppSettings[ConnectionString]);
            }
        }

        public _Database(string ConnectionString, bool IsConn, ref bool isOpen)
        {
            if (IsConn)
            {
                isOpen = this.open2(ConnectionString);
            }
            else
            {
                isOpen = this.open2(ConfigurationManager.AppSettings[ConnectionString]);
            }
        }

        public _Database(string db_host, string db_user, string db_password, string db_name)
        {
            this.open("Data Source=" + db_host + ";User Id=" + db_user + ";Password=" + db_password + ";Initial Catalog=" + db_name + ";Persist Security Info=no;Integrated Security=no;");
        }

        public static void ClarePools()
        {
            SqlConnection.ClearAllPools();
        }

        public void close()
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.Connection.Close();
                this.Connection.Dispose();
            }
        }

        #region 执行sql

        public int ExecuteSql(string SQLString)
        {
            int rows = 0;

            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(SQLString, this.Connection);
                    rows = cmd.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }

            return rows;
        }

        public DataSet Query(string SQLString)
        {
            DataSet ds = new DataSet();
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, this.Connection);
                    command.Fill(ds, "ds");

                    command.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
                finally
                {
                    this.Connection.Close();
                }
            }
            return ds;
        }

        public DataSet Query(string SQLString, int Times)
        {
            DataSet ds = new DataSet();
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, this.Connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");

                    command.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
            return ds;
        }

        #endregion

        #region 存储过程

        public void execute_procedure(string ProcedureName)
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Clear();
                    command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    command.ExecuteNonQuery();
                    command.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
        }

        public void execute_procedure(string ProcedureName, List<SqlParameter> parameters)
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 0;
                    command.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    command.ExecuteNonQuery();
                    command.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
        }

        public void execute_procedure(string ProcedureName, SqlParameter[] parameters)
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    command.ExecuteNonQuery();
                    command.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
        }

        public bool execute_procedure_stone(string ProcedureName, SqlParameter[] parameters, ref string errormsg)
        {
            bool flag = true;
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    command.ExecuteNonQuery();
                    command.Dispose();
                }
                catch (SqlException exception)
                {
                    errormsg = exception.Message;
                    flag = false;
                }
            }
            return flag;
        }

        public void fetch_procedure(string ProcedureName)
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.num_rows = 0;
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.Parameters.Clear();
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables.Count == 1)
                    {
                        this.rows = dataSet.Tables[0].Rows;
                        this.num_rows = this.rows.Count;
                    }
                    else
                    {
                        this.rows = null;
                        this.num_rows = 0;
                    }
                    dataSet.Dispose();
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
        }

        public void fetch_procedure(string ProcedureName, List<SqlParameter> parameters)
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.num_rows = 0;
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            selectCommand.Parameters.Add(parameter);
                        }
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables.Count == 1)
                    {
                        this.rows = dataSet.Tables[0].Rows;
                        this.num_rows = this.rows.Count;
                    }
                    else
                    {
                        this.num_rows = 0;
                    }
                    dataSet.Dispose();
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch
                {
                }
            }
        }

        public void fetch_procedure(string ProcedureName, SqlParameter[] parameters)
        {
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.num_rows = 0;
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            selectCommand.Parameters.Add(parameter);
                        }
                    }
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables.Count == 1)
                    {
                        this.rows = dataSet.Tables[0].Rows;
                        this.num_rows = this.rows.Count;
                    }
                    else
                    {
                        this.rows = null;
                        this.num_rows = 0;
                    }
                    dataSet.Dispose();
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
        }

        public DataSet GetDataSet(string ProcedureName)
        {
            DataSet dataSet = new DataSet();
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.Parameters.Clear();
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    adapter.Fill(dataSet);
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
            return dataSet;
        }

        public DataSet GetDataSet(string ProcedureName, SqlParameter[] parameters)
        {
            DataSet dataSet = new DataSet();
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            selectCommand.Parameters.Add(parameter);
                        }
                    }
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    adapter.Fill(dataSet);
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
            return dataSet;
        }

        public DataTable GetDataTable(string ProcedureName)
        {
            DataTable table = null;
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.num_rows = 0;
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.Parameters.Clear();
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables.Count == 1)
                    {
                        table = dataSet.Tables[0];
                    }
                    dataSet.Dispose();
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
            return table;
        }



        public DataTable GetDataTable(string ProcedureName, SqlParameter[] parameters)
        {
            DataTable table = null;
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.num_rows = 0;
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            selectCommand.Parameters.Add(parameter);
                        }
                    }
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables.Count == 1)
                    {
                        table = dataSet.Tables[0];
                    }
                    dataSet.Dispose();
                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
            return table;
        }



        public DataTable GetDataTable(string ProcedureName, List<SqlParameter> parameters)
        {
            DataTable table = new DataTable();
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.num_rows = 0;
                try
                {
                    SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                    selectCommand.CommandType = CommandType.StoredProcedure;
                    selectCommand.CommandTimeout = 0; selectCommand.Parameters.Clear();
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            selectCommand.Parameters.Add(parameter);
                        }
                    }
                    selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    adapter.Fill(table);

                    adapter.Dispose();
                    selectCommand.Dispose();
                }
                catch (SqlException exception)
                {
                    _Log.SaveMessage(exception.Message);
                }
            }
            return table;
        }

        #endregion

        private void open(string ConnectionString)
        {
            this.num_rows = 0;
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.Connection.Close();
            }
            try
            {
                this.Connection = new SqlConnection(ConnectionString);
                this.Connection.Open();
                this.row = new SortedList();
            }
            catch (SqlException exception)
            {
                _Log.SaveMessage(exception.Message);
            }
        }

        private bool open2(string ConnectionString)
        {
            this.num_rows = 0;
            if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            {
                this.Connection.Close();
            }
            try
            {
                this.Connection = new SqlConnection(ConnectionString);
                this.Connection.Open();
                this.row = new SortedList();
                return true;
            }
            catch (SqlException exception)
            {
                _Log.SaveMessage(exception.Message);
            }
            return false;
        }

        #region IDisposable 成员

        public void Dispose()
        {
            this.close();
        }

        #endregion
    }
}
